﻿CREATE PROCEDURE [SSIS].[ConfigureProjectParameters](
	@SolutionFolderName sysname,
	@EnvironmentName sysname
)
AS
BEGIN
	-- make sure the solution environment exists
	EXEC [SSIS].[CreateSolutionEnvironment]
		@SolutionFolderName = @SolutionFolderName,
		@EnvironmentName = @EnvironmentName;

	--configure each project within the solution
	DECLARE C_SOLUTION_PROJECTS CURSOR FAST_FORWARD FOR(
		SELECT prj.[name]
		FROM [$(SSISDB)].[catalog].[projects] AS prj
		INNER JOIN [$(SSISDB)].[catalog].[folders] AS fld
			ON (fld.[folder_id] = prj.[folder_id])
			AND(fld.[name] = @SolutionFolderName)
	);
	DECLARE @ProjectName sysname;
	OPEN C_SOLUTION_PROJECTS;
	FETCH NEXT FROM C_SOLUTION_PROJECTS INTO @ProjectName;
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		-- add a reference to the environment
		DECLARE @EnvironmentReferenceID bigint = NULL;

		EXEC [SSIS].[CreateProjectEnvironmentReference]
			@SolutionFolderName = @SolutionFolderName,
			@ProjectName = @ProjectName,
			@EnvironmentName = @EnvironmentName,
			@EnvironmentReferenceID = @EnvironmentReferenceID OUTPUT;

		--add environment variables for each project parameter
		EXEC [SSIS].[CreateProjectEnvironmentVariables]
			@SolutionFolderName = @SolutionFolderName,
			@ProjectName = @ProjectName,
			@EnvironmentName = @EnvironmentName;

		FETCH NEXT FROM C_SOLUTION_PROJECTS INTO @ProjectName;
	END
	CLOSE C_SOLUTION_PROJECTS;
	DEALLOCATE C_SOLUTION_PROJECTS;

	RETURN(0);
END
GO
EXECUTE sp_addextendedproperty @name = N'Description', @value = N'This procedure will configure all project parameter default values as their respective solution environment variables.', @level0type = N'SCHEMA', @level0name = N'SSIS', @level1type = N'PROCEDURE', @level1name = N'ConfigureProjectParameters';

